# -*- coding: utf-8 -*-
import re,os,sqlite3,urllib2,datetime
# h='31012011051'
# 3101表示宁波海关、3104表示北仑海关，报关单号开头表示在哪个海关报关
c1='3101'
# 表示年份
y='2011'
# 0表示出口，1表示进口
i='0'
# 51表示宁波海关，54表示北仑海关
c2='51'
h=c1+y+i+c2
s=9680000
e=9681000
global proc
proc=0
total=float(e-s-1)
conn_edi=sqlite3.connect(r'edi.db3')
conn_edi.text_factory = str
conn=sqlite3.connect(r'cd.db3')
conn.text_factory = str
# conn.text_factory = sqlite3.OptimizedUnicode
# c = conn.cursor()
def dbcreate():
    # cd_no                        #报关单号，18位数字
    # cd_customs            #海关
    # cd_customsid        #海关编号，表示货物在哪个海关出口
    # cd_company            #企业名称
    # cd_companyid        #企业编号
    # cd_date                    #报关日期
    # cd_country            #国家
    # cd_countryid        #国家编号
    # cd_port                    #港口
    # cd_portid                #港口编号
    # cd_source                #货源地
    # cd_sourceid            #货源地编号
    # cd_transport        #运输方式
    # cd_transportid    #运输方式编号
    # cd_vessel                #船名
    # cd_voyage                #航次
    # cd_verify                #核销单号，9位数字
    # cd_lading                #提单号，数字和字母，长度不定
    # cd_orderno            #订单号，内容不定，长度不定，
    # cd_box                    #集装箱数量（TEU单位）
    # cd_change                #交割类型
    # cd_unit                    #报关单位
    # cd_unitid                #报关单位编号
    # cd_unitpoint        #报关单位点
    # cd_passno                #通关单号，18位数字
    # cd_goods                #商品名称
    try:
        conn.execute('''CREATE TABLE currency(
            id INTEGER PRIMARY KEY,
            currency TEXT NOT NULL
        )''')
        conn.execute('''CREATE TABLE customs(
            id INTEGER PRIMARY KEY,
            customs TEXT NOT NULL
        )''')
        conn.execute('''CREATE TABLE transport(
            id INTEGER PRIMARY KEY,
            transport TEXT NOT NULL UNIQUE
        )''')
        conn.execute('''CREATE TABLE company(
            id INTEGER PRIMARY KEY,
            company TEXT NOT NULL
        )''')
        conn.execute('''CREATE TABLE country(
            id INTEGER PRIMARY KEY,
            country TEXT NOT NULL
        )''')
        conn.execute('''CREATE TABLE port(
            id INTEGER PRIMARY KEY,
            port TEXT NOT NULL
        )''')
        conn.execute('''CREATE TABLE countryport(
            countryid INTEGER NOT NULL REFERENCES [country]([id]) ON DELETE CASCADE ON UPDATE CASCADE,
            portid INTEGER NOT NULL REFERENCES [port]([id]) ON DELETE CASCADE ON UPDATE CASCADE,
            PRIMARY KEY (countryid,portid)
        )''')
        conn.execute('''CREATE TABLE source(
            id INTEGER PRIMARY KEY,
            source TEXT NOT NULL
        )''')
        conn.execute('''CREATE TABLE vessel(
            id INTEGER PRIMARY KEY,
            vessel TEXT NOT NULL UNIQUE
        )''')
        conn.execute('''CREATE TABLE voyage(
            id INTEGER PRIMARY KEY,
            vesselid INTEGER NOT NULL REFERENCES [vessel]([id]) ON DELETE CASCADE ON UPDATE CASCADE,
            voyage TEXT NOT NULL,
            UNIQUE([vesselid], [voyage]) ON CONFLICT REPLACE
        )''')
        conn.execute('''CREATE TABLE shipline(
            voyageid INTEGER NOT NULL REFERENCES [voyage]([id]) ON DELETE CASCADE ON UPDATE CASCADE,
            portid INTEGER NOT NULL REFERENCES [port]([id]) ON DELETE CASCADE ON UPDATE CASCADE,
            PRIMARY KEY ([voyageid], [portid])
        )''')
        conn.execute('''CREATE TABLE unit(
            id INTEGER PRIMARY KEY,
            unit TEXT NOT NULL UNIQUE
        )''')
        conn.execute('''CREATE TABLE unitpoint(
            id INTEGER PRIMARY KEY,
            unitpoint TEXT NOT NULL UNIQUE
        )''')
        conn.execute('''CREATE TABLE unitbind(
            unitid INTEGER NOT NULL REFERENCES [unit]([id]) ON DELETE CASCADE ON UPDATE CASCADE,
            unitpointid INTEGER NOT NULL REFERENCES [unitpoint]([id]) ON DELETE CASCADE ON UPDATE CASCADE,
            PRIMARY KEY ([unitid], [unitpointid])
        )''')
        conn.execute('''CREATE TABLE change(
            id INTEGER PRIMARY KEY,
            change TEXT NOT NULL UNIQUE
        )''')
        conn.execute('''CREATE TABLE cd(
            cd_no INTEGER PRIMARY KEY,
            cd_customsid INTEGER NOT NULL REFERENCES [customs]([id]) ON DELETE CASCADE ON UPDATE CASCADE,
            cd_companyid INTEGER NOT NULL REFERENCES [company]([id]) ON DELETE CASCADE ON UPDATE CASCADE,
            cd_date DATE,
            cd_countryid INTEGER NOT NULL REFERENCES [country]([id]) ON DELETE CASCADE ON UPDATE CASCADE,
            cd_portid INTEGER NOT NULL REFERENCES [port]([id]) ON DELETE CASCADE ON UPDATE CASCADE,
            cd_sourceid INTEGER NOT NULL REFERENCES [source]([id]) ON DELETE CASCADE ON UPDATE CASCADE,
            cd_transportid INTEGER NOT NULL REFERENCES [transport]([id]) ON DELETE CASCADE ON UPDATE CASCADE,
            cd_voyageid INTEGER NOT NULL REFERENCES [voyage]([id]) ON DELETE CASCADE ON UPDATE CASCADE,
            cd_box INTEGER,
            cd_changeid INTEGER NOT NULL REFERENCES [change]([id]) ON DELETE CASCADE ON UPDATE CASCADE,
            cd_unitid INTEGER NOT NULL REFERENCES [unit]([id]) ON DELETE CASCADE ON UPDATE CASCADE
        )''')
        conn.execute('''CREATE TABLE cdc(
            cd_no INTEGER PRIMARY KEY REFERENCES [cd]([cd_no]) ON DELETE CASCADE ON UPDATE CASCADE,
            cd_verify INTEGER,
            cd_lading TEXT,
            cd_orderno TEXT,
            cd_unitpointid INTEGER NOT NULL REFERENCES [unitpoint]([id]) ON DELETE CASCADE ON UPDATE CASCADE,
            cd_passno INTEGER,
            cd_goods TEXT,
            cd_amount REAL,
            cd_currencyid INTEGER NOT NULL REFERENCES [currency]([id]) ON DELETE CASCADE ON UPDATE CASCADE
        )''')
        conn_edi.execute('''CREATE TABLE edi(
            cd_no INTEGER PRIMARY KEY,
            cd_customs TEXT,
            cd_customsid INTEGER,
            cd_company TEXT,
            cd_companyid INTEGER,
            cd_date DATE,
            cd_country TEXT,
            cd_countryid INTEGER,
            cd_port TEXT,
            cd_portid INTEGER,
            cd_source TEXT,
            cd_sourceid INTEGER,
            cd_transport TEXT,
            cd_vessel TEXT,
            cd_voyage TEXT,
            cd_verify INTEGER,
            cd_lading TEXT,
            cd_orderno TEXT,
            cd_box INTEGER,
            cd_change TEXT,
            cd_unit TEXT,
            cd_unitpoint TEXT,
            cd_passno INTEGER,
            cd_goods TEXT,
            cd_amount REAL,
            cd_currency TEXT,
            cd_currencyid INTEGER
        )''')
        conn.commit()
    except:
        pass
def dbfind(tbl,fld,val):
    return conn.execute('select id from '+tbl+' where '+fld+'="'+val+'"').fetchone()[0]
def dbinsert(cd):
    # 插入海关，如果id存在就覆盖
    conn.execute('replace into customs values(?,?)',(cd["cd_customsid"],cd["cd_customs"]))
    # 插入企业，如果id存在就覆盖
    conn.execute('replace into company values(?,?)',(cd["cd_companyid"],cd["cd_company"]))
    # 插入国家，如果id存在就覆盖
    conn.execute('replace into country values(?,?)',(cd["cd_countryid"],cd["cd_country"]))
    # 插入港口，如果id存在就覆盖
    conn.execute('replace into port values(?,?)',(cd["cd_portid"],cd["cd_port"]))
    # 插入国家港口关系，如果国家id和港口id存在重复就忽略
    conn.execute('insert or ignore into countryport values(?,?)',(cd["cd_countryid"],cd["cd_portid"]))
    # 插入货源地，如果id存在就覆盖
    conn.execute('replace into source values(?,?)',(cd["cd_sourceid"],cd["cd_source"]))
    # 插入报关单位，如果存在就忽略
    conn.execute('insert or ignore into unit values(?,?)',(None,cd["cd_unit"]))
    # 取得报关单位id
    unitid=dbfind('unit','unit',cd["cd_unit"])
    # 插入报关单位点，如果存在就忽略
    conn.execute('insert or ignore into unitpoint values(?,?)',(None,cd["cd_unitpoint"]))
    # 取得报关单位点id
    unitpointid=dbfind('unitpoint','unitpoint',cd["cd_unitpoint"])
    # 插入报关单位和报关单位点的关系
    # if conn.execute('select 1 from unitbind where unitid=? and unitpointid=?',(unitid,unitpointid)).fetchone()==None:
        # conn.execute('insert into unitbind values(?,?)',(unitid,unitpointid))
    conn.execute('replace into unitbind values(?,?)',(unitid,unitpointid))
    # 插入运输方式，如果存在就忽略
    conn.execute('insert or ignore into transport values(?,?)',(None,cd["cd_transport"]))
    # 取得运输方式id
    transportid=dbfind('transport','transport',cd["cd_transport"])
    # 插入船名，如果存在就忽略
    conn.execute('insert or ignore into vessel values(?,?)',(None,cd["cd_vessel"]))
    # 取得船名id
    vesselid=dbfind('vessel','vessel',cd["cd_vessel"])
    # 获取航线id，如果有相同的船名，但没有这个航次，就新插入一个航次
    voyage=conn.execute('select id from voyage where vesselid=? and voyage=?',(vesselid,cd["cd_voyage"])).fetchone()
    if voyage==None:
        voyageid=conn.execute('insert into voyage values(?,?,?)',(None,vesselid,cd["cd_voyage"])).lastrowid
        # conn.execute('insert into voyage values(?,?,?)',(None,vesselid,cd["cd_voyage"]))
        # voyageid=conn.execute('select last_insert_rowid()').fetchone()[0]
    else:
        voyageid=voyage[0]
    # 插入航次和港口的关系
    # if conn.execute('select 1 from shipline where voyageid=? and portid=?',(voyageid,cd["cd_portid"])).fetchone()==None:
        # conn.execute('insert into shipline values(?,?)',(voyageid,cd["cd_portid"]))
    conn.execute('replace into shipline values(?,?)',(voyageid,cd["cd_portid"]))
    # 插入交割类型，如果存在就忽略
    conn.execute('insert or ignore into change values(?,?)',(None,cd["cd_change"]))
    # 获取交割类型id
    changeid=dbfind('change','change',cd["cd_change"])
    # 插入币种，如果id存在就覆盖
    conn.execute('replace into currency values(?,?)',(cd["cd_currencyid"],cd["cd_currency"]))
    # 插入主表
    conn.execute('insert or replace into cd values(?,?,?,?,?,?,?,?,?,?,?,?)',\
    (cd["cd_no"],cd["cd_customsid"],cd["cd_companyid"],cd["cd_date"],cd["cd_countryid"],cd["cd_portid"],cd["cd_sourceid"],transportid,voyageid,cd["cd_box"],changeid,unitid))
    # 插入次表，用报关单号关联主表，保存一些次要的信息，目的使信息完整
    conn.execute('insert or replace into cdc values(?,?,?,?,?,?,?,?,?)',\
    (cd["cd_no"],cd["cd_verify"],cd["cd_lading"],cd["cd_orderno"],unitpointid,cd["cd_passno"],cd["cd_goods"],cd["cd_amount"],cd["cd_currencyid"]))
    # 插入原始表
    conn_edi.execute('insert or replace into edi values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)',\
    (cd["cd_no"],cd["cd_customs"],cd["cd_customsid"],cd["cd_company"],cd["cd_companyid"],cd["cd_date"],cd["cd_country"],cd["cd_countryid"],cd["cd_port"],cd["cd_portid"],cd["cd_source"],cd["cd_sourceid"],cd["cd_transport"],cd["cd_vessel"],cd["cd_voyage"],cd["cd_verify"],cd["cd_lading"],cd["cd_orderno"],cd["cd_box"],cd["cd_change"],cd["cd_unit"],cd["cd_unitpoint"],cd["cd_passno"],cd["cd_goods"],cd["cd_amount"],cd["cd_currency"],cd["cd_currencyid"]))
def gethtml(cd_no,sign=1):
    url='http://www.nbedi.com/h2000eport_ic/pre_bgd/EntryLogDataSearch/bgd_print/normal_print.asp?sign='+str(sign)+r'&pre_entry_id='+str(cd_no)
    # print url
    content=urllib2.urlopen(url).read()
    if(sign!=1):
        f=open(cd_no+r'.'+str(sign),'w')
    else:
        f=open(cd_no,'w')
    f.write(content)
    f.close()
    return content
dbcreate()
starttime=datetime.datetime.now()
for x in range(s,e):
    curr=int(((x-s)*100/total))
    if curr<>proc:
        proc=curr
        currtime=datetime.datetime.now()
        print proc,'%',currtime.time(),currtime-starttime
        
    cd={}
    sign=1
    fname=h+str(x)
    if(os.path.isfile(fname)):
        f=open(fname)
        html=f.read()
        f.close()
    else:
        html=gethtml(fname)
    # 报关单号是否存在
    try:
        re.search(fname,html).group(0)
    except:
        # os.remove(fname)
        continue
    # 是否是样本
    # pattern=unicode(r"<font size='4'>样&nbsp;&nbsp;本</font>",'utf8')
    # sample=re.search(pattern,html.decode('gbk'))
    pattern=r"<font size='4'>.+?</font>"
    sample=re.search(pattern,html)
    if(sample!=None):
        fs=open('sample.txt','a')
        fs.write(fname+'\n')
        fs.close()
        continue
    # 海关
    try:
        pattern=r'<div id="Layer2" style="position:absolute; width:130px; height:16px; z-index:2; left: 66px; top: 132px;">.+?</div>'
        cd_customs=re.search(pattern,html).group(0)
        pattern=r'<.+?>'
        cd_customs=re.sub(pattern,'',cd_customs)
        pattern=r'<div id="Layer2" style="position:absolute; width:107px; height:16px; z-index:2; left: 204px; top: 132px;">.+?</div>'
        cd_customsid=re.search(pattern,html).group(0)
        pattern=r'<.+?>'
        cd_customsid=re.sub(pattern,'',cd_customsid)
        pattern=r'\d+'
        cd_customsid=int(re.search(pattern,cd_customsid).group(0))
    except:
        continue
    # 企业名称
    try:
        pattern=r'<div id="Layer2" style="position:absolute; width:232px; height:18px; z-index:2; left: 80px; top: 154px;">.+?</div>'
        cd_company=re.search(pattern,html).group(0)
        pattern=r'<.+?>'
        cd_company=re.sub(pattern,'',cd_company)
        pattern=r'<div id="Layer2" style="position:absolute; width:114px; height:15px; z-index:2; left: 36px; top: 165px;">.+?</div>'
        cd_companyid=re.search(pattern,html).group(0)
        pattern=r'<.+?>'
        cd_companyid=int(re.sub(pattern,'',cd_companyid))
    except:
        continue
        # cd_company=None
        # cd_companyid=None
    # 日期
    try:
        pattern=r'<div id="Layer2" style="position:absolute; width:126px; height:18px; z-index:2; left: 605px; top: 132px;">.+?</div>'
        cd_date=re.search(pattern,html).group(0)
        pattern=r'<.+?>'
        cd_date=re.sub(pattern,'',cd_date)
    except:
        continue
        # cd_date=None
    # 国家
    try:
        pattern=r'<div id="Layer2" style="position:absolute; width:180px; height:15px; z-index:2; left: 216px; top: 221px;">.+?</div>'
        country=re.search(pattern,html).group(0)
        pattern=r'<.+?>'
        country=re.sub(pattern,'',country)
        lCountry=country.split('&nbsp;&nbsp;&nbsp;')
        cd_country=lCountry[0]
        cd_countryid=lCountry[1]
        pattern=r'\d+'
        cd_countryid=int(re.search(pattern,cd_countryid).group(0))
    except:
        continue
        # cd_country=None
        # cd_countryid=None
    # 港口
    try:
        pattern=r'<div id="Layer2" style="position:absolute; width:184px; height:15px; z-index:2; left: 400px; top: 223px;">.+?</div>'
        port=re.search(pattern,html).group(0)
        pattern=r'<.+?>'
        port=re.sub(pattern,'',port)
        lPort=port.split('&nbsp;&nbsp;&nbsp;')
        cd_port=lPort[0]
        cd_portid=lPort[1]
        pattern=r'\d+'
        cd_portid=int(re.search(pattern,cd_portid).group(0))
    except:
        continue
        # cd_port=None
        # cd_portid=None
    # 货源地
    try:
        pattern=r'<div id="Layer2" style="position:absolute; width:139px; height:15px; z-index:2; left: 586px; top: 223px;">.+?</div>'
        source=re.search(pattern,html).group(0)
        pattern=r'<.+?>'
        source=re.sub(pattern,'',source)
        lSource=source.split('&nbsp;&nbsp;&nbsp;')
        cd_source=lSource[0]
        cd_sourceid=lSource[1]
        pattern=r'\d+'
        cd_sourceid=int(re.search(pattern,cd_sourceid).group(0))
    except:
        continue
        # cd_source=None
        # cd_sourceid=None
    # 运输方式
    try:
        pattern=r'<div id="Layer2" style="position:absolute; width:67px; height:15px; z-index:2; left: 313px; top: 163px;">.+?</div>'
        cd_transport=re.search(pattern,html).group(0)
        pattern=r'<.+?>'
        cd_transport=re.sub(pattern,'',cd_transport)
    except:
        continue
    # 船名航次
    try:
        pattern=r'<div id="Layer2" style="position:absolute; width:191px; height:15px; z-index:2; left: 380px; top: 164px;">.+?</div>'
        cd_vv=re.search(pattern,html).group(0)
        pattern=r'<.+?>'
        cd_vv=re.sub(pattern,'',cd_vv)
        lVV=cd_vv.split('/')
        cd_vessel=lVV[0]
        cd_voyage=lVV[1]
    except:
        continue
        # cd_vessel=None
        # cd_voyage=None
    # 核销单号
    try:
        pattern=r'<div id="Layer2" style="position:absolute; width:143px; height:15px; z-index:2; left: 68px; top: 249px;">.+?</div>'
        cd_verify=re.search(pattern,html).group(0)
        pattern=r'<.+?>'
        cd_verify=int(re.sub(pattern,'',cd_verify))
    except:
        cd_verify=None
    # 提单号
    try:
        pattern=r'<div id="Layer2" style="position:absolute; width:160px; height:15px; z-index:2; left: 571px; top: 165px;">.+?</div>'
        cd_lading=re.search(pattern,html).group(0)
        pattern=r'<.+?>'
        cd_lading=re.sub(pattern,'',cd_lading)
    except:
        cd_lading=None
    # 订单号
    try:
        pattern=r'<div id="Layer2" style="position:absolute; width:143px; height:15px; z-index:2; left: 77px; top: 282px;">.+?</div>'
        cd_orderno=re.search(pattern,html).group(0)
        pattern=r'<.+?>'
        cd_orderno=re.sub(pattern,'',cd_orderno)
    except:
        cd_orderno=None
    # 箱数
    try:
        # 去掉换行
        pattern=r'\n'
        box=re.sub(pattern,'',html)
        pattern=r'<div id="Layer2" style="position:absolute; width:175px; height:15px; z-index:2; left: 66px; top: 320px;">.+?</div>'
        box=re.search(pattern,box).group(0)
        pattern=r'\(\d+\)'
        # box=re.search(pattern,box).group(0)
        cd_box=re.search(pattern,box)
        if(cd_box==None):
            pattern=r'\* \d+'
            cd_box=re.search(pattern,box).group(0)
        else:
            cd_box=cd_box.group(0)
        pattern=r'\d+'
        cd_box=re.search(pattern,cd_box).group(0)
    except:
        cd_box=None
    # 交割类型
    try:
        pattern=r'\n'
        cd_change=re.sub(pattern,'',html)
        pattern=r'<div id="Layer2" style="position:absolute; width:65px; height:15px; z-index:2; left: 233px; top: 251px;">.+?</div>'
        cd_change=re.search(pattern,cd_change).group(0)
        pattern=r'<DIV ALIGN="CENTER">.+?</DIV>'
        cd_change=re.search(pattern,cd_change).group(0)
        pattern=r'<.+?>'
        cd_change=re.sub(pattern,'',cd_change)
    except:
        continue
        # cd_change=None
    # 报关单位
    try:
        pattern=r'\n'
        cd_unit=re.sub(pattern,'',html)
        pattern=r'<div id="Layer3" style="position:absolute; width:236px; height:23px; z-index:5; left: 238px; top: 917px;">.+?</div>'
        cd_unit=re.search(pattern,cd_unit).group(0)
        pattern=r'<div align="center">.+?</div>'
        cd_unit=re.search(pattern,cd_unit).group(0)
        pattern=r'<.+?>'
        cd_unit=re.sub(pattern,'',cd_unit)
    except:
        continue
        # cd_unit=None
    # 报关单位点
    try:
        pattern=r'<div id="Layer3" style="position:absolute; width:215px; height:16px; z-index:5; left: 17px; top: 841px;">.+?</div>'
        cd_unitpoint=re.search(pattern,html).group(0)
        pattern=r'<.+?>'
        cd_unitpoint=re.sub(pattern,'',cd_unitpoint)
    except:
        cd_unitpoint=None
    # 通关单号
    try:
        pattern=r'B:\d{18}'
        # 通关单号可能会没有
        cd_passno=re.search(pattern,html).group(0)
        cd_passno=cd_passno[2:]
    except:
        cd_passno=None
    
    # 计算货物数、金额和币种
    iAmount=0
    pattern=r'<td width="7%" ><div align="center">.+?</div></td>'
    cd_currencyid=re.search(pattern,html).group(0)
    pattern=r'<.+?>'
    cd_currencyid=re.sub(pattern,'',cd_currencyid)
    pattern=r'<td> <div align="center">.+?</div></td>'
    cd_currency=re.search(pattern,html).group(0)
    pattern=r'<.+?>'
    cd_currency=re.sub(pattern,'',cd_currency).strip()
    pattern=r'<td width="3%"  rowspan="2" align="center">'
    iName=len(re.findall(pattern,html))
    pattern=r'<td width="19%">.+?</td>'
    pattern_a=r'<td width="11%"><div align="center">.+?</div>'
    lName=re.findall(pattern,html)
    lAmount=re.findall(pattern_a,html)
    for x in range(iName):
        pattern=r'<.+?>'
        lName[x]=re.sub(pattern,'',lName[x])
        iAmount+=float(re.sub(pattern,'',lAmount[x]))
    while iName==5:
        # 去抓下一页
        sign+=1
        if(os.path.isfile(fname+r'.'+str(sign))):
            f=open(fname+r'.'+str(sign))
            morehtml=f.read()
            f.close()
        else:
            morehtml=gethtml(fname,sign)
        
            # 用curl去抓取
            # url=r'curl.exe -# -o '+fname+r'.'+str(sign)+r' "http://www.nbedi.com/h2000eport_ic/pre_bgd/EntryLogDataSearch/bgd_print/normal_print.asp?sign='+str(sign)+r'&pre_entry_id='+fname+r'"'
            # os.popen(url)
            # morehtml=open(fname+r'.'+str(sign)).read()
            
        pattern=r'<td width="3%"  rowspan="2" align="center">'
        iName=len(re.findall(pattern,morehtml))
        pattern=r'<td width="19%">.+?</td>'
        lMore=re.findall(pattern,morehtml)
        lAmount=re.findall(pattern_a,morehtml)
        for x in range(iName):
            pattern=r'<.+?>'
            lMore[x]=re.sub(pattern,'',lMore[x])
            iAmount+=float(re.sub(pattern,'',lAmount[x]))
        lName.extend(lMore)
    cd_goods=','.join(list(set(lName)))
    cd_amount=iAmount
    # print fname,cd_company,cd_companyid,cd_date,cd_box
    # print cd_customs,cd_customsid,cd_transport
    # print cd_currencyid,cd_currency,cd_amount
    # print cd_country,cd_countryid,cd_port,cd_portid,cd_source,cd_sourceid,cd_voyage,cd_verify,cd_lading
    # print cd_orderno,cd_box,cd_change
    # if(cd_unit!=cd_unitpoint):
        # print cd_unit,cd_unitpoint
    # print cd_passno,cd_goods
    cd["cd_no"]=fname
    # cd["cd_company"]=cd_company
    cd["cd_company"]=unicode(cd_company,'gb18030')
    cd["cd_companyid"]=cd_companyid
    # cd["cd_customs"]=cd_customs
    cd["cd_customs"]=unicode(cd_customs,'gb18030')
    cd["cd_customsid"]=cd_customsid
    cd["cd_date"]=cd_date
    # cd["cd_country"]=cd_country
    cd["cd_country"]=unicode(cd_country,'gb18030')
    cd["cd_countryid"]=cd_countryid
    # cd["cd_port"]=cd_port
    cd["cd_port"]=unicode(cd_port,'gb18030')
    cd["cd_portid"]=cd_portid
    # cd["cd_source"]=cd_source
    cd["cd_source"]=unicode(cd_source,'gb18030')
    cd["cd_sourceid"]=cd_sourceid
    # cd["cd_transport"]=cd_transport
    cd["cd_transport"]=unicode(cd_transport,'gb18030')
    cd["cd_vv"]=cd_vv
    cd["cd_vessel"]=cd_vessel
    cd["cd_voyage"]=cd_voyage
    cd["cd_verify"]=cd_verify
    cd["cd_lading"]=cd_lading
    # cd["cd_orderno"]=cd_orderno
    if(cd_orderno!=None):
        cd["cd_orderno"]=unicode(cd_orderno,'gb18030')
    else:
        cd["cd_orderno"]=cd_orderno
    cd["cd_box"]=cd_box
    cd["cd_change"]=unicode(cd_change,'gb18030')
    # cd["cd_unit"]=cd_unit
    cd["cd_unit"]=unicode(cd_unit,'gb18030')
    # cd["cd_unitpoint"]=cd_unitpoint
    # if(cd_unitpoint!=None):
        # cd["cd_unitpoint"]=unicode(cd_unitpoint,'gb18030')
    # else:
        # cd["cd_unitpoint"]=cd_unitpoint
    cd["cd_unitpoint"]=unicode(cd_unitpoint,'gb18030')
    cd["cd_passno"]=cd_passno
    # cd["cd_goods"]=cd_goods
    cd["cd_goods"]=unicode(cd_goods,'gb18030')
    cd["cd_amount"]=cd_amount
    cd["cd_currency"]=unicode(cd_currency,'gb18030')
    cd["cd_currencyid"]=cd_currencyid
    # print cd
    # if not dbfind(fname):
    dbinsert(cd)
    # else:
        # dbupdate(cd)
conn.commit()
conn_edi.commit()
conn.close()
conn_edi.close()
